CREATE PROCEDURE [dbo].[ProviderGet]
	@Id				UNIQUEIDENTIFIER,
	@SpecialtyId	UNIQUEIDENTIFIER,
	@ClinicTypeId	UNIQUEIDENTIFIER,
	@ZipCode		FLOAT,
	@Proximity		FLOAT,
	@Keywords		NVARCHAR(50)
	
AS

DECLARE @FromLat FLOAT
DECLARE @FromLong FLOAT
DECLARE @pProximity FLOAT


Select @FromLat = lat,
@FromLong = lon
from ZipCode
Where Zip IS NULL OR Zip = @ZipCode

Select A.* from (select dbo.fnProximityGet(@FromLat,@FromLong,Latitude,Longitude) AS Proximity ,*
from Provider P) AS A
INNER JOIN ProviderClinicType PCT ON PCT.ProviderId = A.Id
INNER JOIN ClinicType CT ON PCT.ClinicTypeId = CT.Id
INNER JOIN Specialty S ON CT.SpecialtyId = S.Id
WHERE 
	(@Id IS NULL OR A.Id = @Id)
	AND (@SpecialtyId IS NULL OR S.Id = @SpecialtyId)
	AND (@ClinicTypeId IS NULL OR CT.Id = @ClinicTypeId)
	AND (@Keywords IS NULL OR (A.ConditionsTreated LIKE @Keywords OR A.ProceduresPerformed LIKE @Keywords OR A.Exclusions LIKE @Keywords OR A.AlternativeServices LIKE @Keywords))
	AND (@Proximity IS NULL OR A.Proximity <= @Proximity)
	ORDER BY A.Proximity
	
RETURN @@ROWCOUNT